package com.central.dataManage.common.datasourcePlugin;

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLDataTypeImpl;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.expr.*;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.dialect.oracle.ast.stmt.OracleAlterTableModify;
import com.alibaba.druid.sql.dialect.oracle.ast.stmt.OracleCreateIndexStatement;
import com.alibaba.druid.sql.dialect.oracle.ast.stmt.OracleCreateTableStatement;
import com.alibaba.druid.sql.dialect.oracle.ast.stmt.OraclePrimaryKey;
import com.alibaba.druid.sql.parser.ParserException;
import com.alibaba.druid.sql.parser.SQLParserUtils;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import com.alibaba.druid.util.JdbcConstants;
import com.central.common.enums.DbType;
import com.central.common.model.ColumnInfo;
import com.central.common.model.IndexInfo;
import com.central.dataManage.common.utils.CommonUtils;
import com.central.dataManage.model.DatabaseInfo;
import com.central.common.model.DatasourceInfo;
import com.central.common.model.TableInfo;
import org.apache.commons.lang3.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;

import static com.central.dataManage.common.Constants.COM_ORACLE_JDBC_DRIVER;

/**
 * @author Tindy
 * @date 2021/7/5
 * @describe oracle数据源插件
 */
public class OracleDatasourcePlugin extends BaseDatasourcePlugin {
    public OracleDatasourcePlugin(DatasourceInfo datasource) {
        super(datasource);
    }

    @Override
    public String getJdbcUrl() {
        String address = "jdbc:oracle:thin:@//" + getDatasource().getHost() + ":" + getDatasource().getPort() + "/";
        if (address.lastIndexOf("/") != (address.length() - 1)) {
            address += "/";
        }
        String jdbcUrl = address;
        if (!StringUtils.isBlank(getDatasource().getDbName())) {
            jdbcUrl = address + getDatasource().getDbName();
        }
        String other = buildOther(getDatasource().getOther(), "&");
        if (StringUtils.isNotEmpty(other)) {
            jdbcUrl += "?" + other;
        }
        return jdbcUrl;
    }


    @Override
    public Connection getConn() throws SQLException {
        Connection conn = null;
        try {
            String driver=StringUtils.isNotEmpty(getDatasource().getDriver())?getDatasource().getDriver():getDatasourceDriver();
            Class.forName(driver);
            conn = DriverManager.getConnection(getJdbcUrl(), getDatasource().getUsername(), CommonUtils.decodePassword(getDatasource().getPassword()));
        } catch (ClassNotFoundException e) {
            logger.error(e.getMessage(), e);
        }
        return conn;
    }

    @Override
    public List<DatabaseInfo> getDatabases(Connection conn) throws SQLException {
        List<DatabaseInfo> databases = new ArrayList<>();
        DatabaseInfo database = new DatabaseInfo();
        //oracle数据库名为登录用户的大写
        database.setDbName(getDatasource().getUsername().toUpperCase());
        database.setDatasourceId(getDatasource().getId());
        database.setDatasourceType(DbType.ORACLE);
        databases.add(database);
        return databases;
    }

    @Override
    public List<TableInfo> getTables(DatabaseInfo database, Connection conn) throws SQLException {
        ArrayList<TableInfo> tables = new ArrayList<>();
        Statement statement = conn.createStatement();
        ResultSet resultSet = statement.executeQuery("select table_name from user_tables");
        while (resultSet.next()) {
            String tableName = resultSet.getString(1);
            TableInfo table = new TableInfo();
            table.setDatasourceId(getDatasource().getId());
            table.setDatabaseId(database.getId());
            table.setDbName(database.getDbName());
            table.setTableName(tableName);
            table.setDatasourceType(DbType.ORACLE);
            tables.add(table);
        }
        resultSet.close();
        statement.close();
        return tables;
    }

    @Override
    public List<ColumnInfo> getColumns(DatabaseInfo database, TableInfo table, Connection conn) throws SQLException {
        LinkedHashMap<String, ColumnInfo> columnMap = new LinkedHashMap<>();
        Statement statement = conn.createStatement();
        ResultSet resultSet = statement.executeQuery("select dbms_metadata.get_ddl('TABLE','" + table.getTableName() + "') from dual");
        resultSet.next();
        String createTableDdl = resultSet.getString(1);
        table.setDdl(createTableDdl);
        SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(createTableDdl, JdbcConstants.ORACLE);
        List<SQLStatement> stmtList = parser.parseStatementList();
        for (SQLStatement stmt : stmtList) {
            OracleCreateTableStatement createTable = (OracleCreateTableStatement) stmt;
            for (SQLTableElement sqlTableElement : createTable.getTableElementList()) {
                if (sqlTableElement instanceof SQLColumnDefinition) {
                    SQLColumnDefinition element = (SQLColumnDefinition) sqlTableElement;
                    ColumnInfo column = new ColumnInfo();
                    String columnName = element.getName().toString();
                    if (columnName.matches("\".*\"")) {
                        columnName = columnName.substring(1, columnName.length() - 1);
                    }
                    column.setColumnName(columnName);
                    column.setDataType(element.getDataType().getName());
                    String args = "";
                    for (SQLExpr sqlExpr : element.getDataType().getArguments()) {
                        args += sqlExpr.toString() + ",";
                    }
                    if (!StringUtils.isBlank(args)) {
                        args = args.substring(0, args.length() - 1);
                    }
                    column.setArgs(args);
                    column.setDefVal(element.getDefaultExpr() == null || element.getDefaultExpr() instanceof SQLNullExpr ? "" : element.getDefaultExpr().toString());
                    for (SQLColumnConstraint sqlColumnConstraint : element.getConstraints()) {
                        if (sqlColumnConstraint instanceof SQLNotNullConstraint) {
                            column.setIsNotNull(true);
                            break;
                        }
                    }
                    columnMap.put(column.getColumnName(), column);
                } else if (sqlTableElement instanceof OraclePrimaryKey) {
                    OraclePrimaryKey element = (OraclePrimaryKey) sqlTableElement;
                    for (SQLSelectOrderByItem sqlSelectOrderByItem : element.getColumns()) {
                        String primaryKeyColumn = sqlSelectOrderByItem.getExpr().toString();
                        if (primaryKeyColumn.matches("\".*\"")) {
                            primaryKeyColumn = primaryKeyColumn.substring(1, primaryKeyColumn.length() - 1);
                        }
                        ColumnInfo column = columnMap.get(primaryKeyColumn);
                        column.setIsPrimkey(true);
                    }
                }
            }
        }
        ResultSet resultSet2 = statement.executeQuery("select COLUMN_NAME,COMMENTS from user_col_comments a  where a.table_name='" + table.getTableName() + "'");
        while (resultSet2.next()) {
            String columnName = resultSet2.getString(1);
            String columnComment = resultSet2.getString(2);
            ColumnInfo column = columnMap.get(columnName);
            if (column != null) {
                column.setColumnComment(columnComment);
            }
        }
        ResultSet resultSet3 = statement.executeQuery("select COMMENTS from user_tab_comments a  where  a.table_name='" + table.getTableName() + "'");
        resultSet3.next();
        table.setTableComment(resultSet3.getString(1));
        statement.close();
        return new ArrayList(columnMap.values());
    }

    @Override
    public List<IndexInfo> getIndexs(DatabaseInfo database, TableInfo table, Connection conn) throws SQLException {
        List<IndexInfo> indexs = new ArrayList<>();
        Statement statement = conn.createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT " +
                " UI.INDEX_NAME, " +
                " UI.UNIQUENESS, " +
                " wm_concat ( IC.COLUMN_NAME )  " +
                "FROM " +
                " user_indexes UI, " +
                " user_ind_columns IC  " +
                "WHERE " +
                " UI.table_name = '" + table.getTableName() + "' " +
                " AND UI.INDEX_TYPE != 'LOB'  " +
                " AND UI.INDEX_TYPE != 'DOMAIN'  " +
                " AND UI.INDEX_TYPE != 'CLUSTER'  " +
                " AND UI.index_name NOT IN ( SELECT index_name FROM user_constraints WHERE table_name = '" + table.getTableName() + "' AND constraint_type = 'P' )  " +
                " AND UI.TABLE_NAME = IC.TABLE_NAME  " +
                " AND UI.INDEX_NAME = IC.INDEX_NAME  " +
                "GROUP BY " +
                " UI.INDEX_NAME, " +
                " UI.UNIQUENESS");
        while (resultSet.next()) {
            IndexInfo index = new IndexInfo();
            index.setIndexName(resultSet.getString(1));
            index.setIsUnique(resultSet.getString(2).equals("UNIQUE"));
            index.setColumns(resultSet.getString(3));
            indexs.add(index);
        }
        resultSet.close();
        statement.close();
        return indexs;
    }

    @Override
    public void createNewDatabase(String dbName) throws SQLException {
        throw new SQLException("oracle类型数据源不能创建新数据库");
    }

    @Override
    public void dropDatabase(String dbName) throws SQLException {
        throw new SQLException("oracle类型数据源不能删除数据库");
    }

    @Override
    public void dropTable(String dbName, String tableName) throws SQLException {
        Statement statement = getConn().createStatement();
        statement.execute("drop table \"" + tableName + "\"");
        statement.close();
    }

    @Override
    public List<SQLStatement> checkCreateTableDdl(String ddl) throws SQLException {
        SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(ddl, JdbcConstants.ORACLE);
        List<SQLStatement> sqlStatements = parser.parseStatementList();
        if(!(sqlStatements.get(0) instanceof SQLCreateTableStatement)){
            throw new ParserException("第一条sql必须是建表语句");
        }
        for (int i=1;i<sqlStatements.size();i++) {
            if (!(sqlStatements.get(i) instanceof SQLDDLStatement || sqlStatements.get(i) instanceof SQLCommentStatement)) {
                throw new ParserException("建表语句类型错误");
            }
        }
        return sqlStatements;
    }

    @Override
    public void createTableByDdl(DatabaseInfo database, List<SQLStatement> sqlStatements) throws SQLException {
        Connection conn = getConn();
        String tableName=null;
        Statement statement =null;
        try {
            statement = conn.createStatement();
            String sql = sqlStatements.get(0).toString();
            statement.execute(sql.substring(0, sql.length() - 1));
            tableName = getTableNameByDdl(sql.substring(0, sql.length() - 1));
            for (int i = 1; i < sqlStatements.size(); i++) {
                sql = sqlStatements.get(i).toString();
                statement.execute(sql.substring(0, sql.length() - 1));
            }
        }catch (Exception e){
            if(tableName!=null){
                statement.execute("drop table \""+tableName+"\"");
            }
            throw e;
        }finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public String generateDdlSql(TableInfo table) {
        //创建注释和索引的sql列表
        ArrayList<String> commensAndIndexs = new ArrayList<>();
        OracleCreateTableStatement statement = new OracleCreateTableStatement();
        statement.setName("\""+table.getTableName()+"\"");
        //表注释
        if (table.getTableComment() != null) {
            SQLCommentStatement commentStatement = new SQLCommentStatement();
            commentStatement.setComment(new SQLCharExpr(table.getTableComment()));
            commentStatement.setType(SQLCommentStatement.Type.TABLE);
            commentStatement.setOn(new SQLIdentifierExpr("\""+table.getTableName()+"\""));
            commensAndIndexs.add(commentStatement.toString());
        }
        //表字段和主键
        List<SQLTableElement> tableElementList = statement.getTableElementList();
        OraclePrimaryKey primaryKey = new OraclePrimaryKey();
        List<ColumnInfo> columns = table.getColumns();
        for (ColumnInfo column : columns) {
            SQLColumnDefinition element = new SQLColumnDefinition();
            element.setName("\""+column.getColumnName()+"\"");
            SQLDataTypeImpl sqlDataType = new SQLDataTypeImpl(column.getDataType());
            if (!StringUtils.isBlank(column.getArgs())) {
                String[] args = column.getArgs().split(",");
                for (String arg : args) {
                    sqlDataType.addArgument(new SQLIntegerExpr(Integer.parseInt(arg)));
                }
            }
            element.setDataType(sqlDataType);
            if (column.getIsNotNull()) {
                element.addConstraint(new SQLNotNullConstraint());
            }
            if (!StringUtils.isBlank(column.getDefVal())) {
                element.setDefaultExpr(new SQLCharExpr(column.getDefVal()));
            }
            if (column.getIsPrimkey()) {
                primaryKey.addColumn(new SQLIdentifierExpr("\""+column.getColumnName()+"\""));
            }
            tableElementList.add(element);
            //字段注释
            if (column.getColumnComment() != null) {
                addCommen(table, commensAndIndexs, column);
            }
        }
        if (primaryKey.getColumns().size() > 0) {
            tableElementList.add(primaryKey);
        }
        //索引
        for (IndexInfo index : table.getIndexs()) {
            OracleCreateIndexStatement indexStatement = new OracleCreateIndexStatement();
            if (index.getIsUnique()) {
                indexStatement.setType("UNIQUE");
            }
            indexStatement.setName(new SQLIdentifierExpr("\""+index.getIndexName()+"\""));
            indexStatement.setTable(new SQLIdentifierExpr("\""+table.getTableName()+"\""));
            for (String s : index.getColumns().split(",")) {
                indexStatement.getItems().add(new SQLSelectOrderByItem(new SQLIdentifierExpr("\""+s+"\"")));
            }
            commensAndIndexs.add(indexStatement.toString());
        }
        StringBuilder result = new StringBuilder();
        result.append(statement.toString()).append(";");
        for (String commensAndIndex : commensAndIndexs) {
            result.append(commensAndIndex).append(";");
        }
        return result.toString();
    }

    @Override
    public String getTableNameByDdl(String ddl) {
        SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(ddl, JdbcConstants.ORACLE);
        SQLCreateTableStatement sqlCreateTableStatement = parser.getSQLCreateTableParser().parseCreateTable();
        String tableName = sqlCreateTableStatement.getName().getSimpleName();
        //oracle建表时如果没有"",会把名称转为大写
        if(tableName.matches("\".*\"")){
            tableName=tableName.substring(1,tableName.length()-1);
        }else {
            tableName=tableName.toUpperCase();
        }
        return tableName;
    }

    @Override
    public String getTableNameByModifyDdl(SQLStatement sqlStatement) {
        String tableName=null;
        if(sqlStatement instanceof SQLAlterTableStatement){
            SQLAlterTableStatement sqlAlterTableStatement = (SQLAlterTableStatement) sqlStatement;
            tableName=sqlAlterTableStatement.getTableName();
        }else {
            SQLCommentStatement sqlCommentStatement = (SQLCommentStatement) sqlStatement;
            tableName = ((SQLPropertyExpr) sqlCommentStatement.getOn().getExpr()).getOwnernName();

        }
        if(tableName.matches("\".*\"")){
            tableName=tableName.substring(1,tableName.length()-1);
        }else {
            tableName=tableName.toUpperCase();
        }
        return tableName;
    }

    @Override
    public String generateModifySql(TableInfo table, TableInfo oldTable) throws Exception {
        List<ColumnInfo> columns = table.getColumns();
        List<ColumnInfo> oldColumns = oldTable.getColumns();
        //创建注释和索引的sql列表
        ArrayList<String> commens = new ArrayList<>();
        SQLAlterTableStatement statement = new SQLAlterTableStatement(JdbcConstants.ORACLE);
        statement.setName(new SQLIdentifierExpr("\""+table.getTableName()+"\""));
        //修改列
        int i=0;
        for (;i<oldColumns.size();i++){
            ColumnInfo column = columns.get(i);
            if(!column.getColumnName().equals(oldColumns.get(i).getColumnName())){
                throw new Exception("修改列字段与原表不匹配");
            }
            ColumnInfo oldColumn = oldColumns.get(i);
            Boolean isModify=false;
            OracleAlterTableModify modifyColumn = new OracleAlterTableModify();
            SQLColumnDefinition element = new SQLColumnDefinition();
            element.setName("\""+column.getColumnName()+"\"");
            if(!column.getDataType().equals(oldColumn.getDataType()) || !column.getArgs().equals(oldColumn.getArgs())){
                SQLDataTypeImpl sqlDataType = new SQLDataTypeImpl(column.getDataType());
                if(!StringUtils.isBlank(column.getArgs())){
                    String[] args = column.getArgs().split(",");
                    for (String arg : args) {
                        sqlDataType.addArgument(new SQLIntegerExpr(Integer.parseInt(arg)));
                    }
                }
                element.setDataType(sqlDataType);
                isModify=true;
            }
            if(column.getIsNotNull()!=oldColumn.getIsNotNull()){
                if(column.getIsNotNull()){
                    element.addConstraint(new SQLNotNullConstraint());
                }else {
                    element.addConstraint(new SQLNullConstraint());
                }
                isModify=true;
            }
            if(!column.getDefVal().equals(oldColumn.getDefVal())){
                if(!StringUtils.isBlank(column.getDefVal())){
                    element.setDefaultExpr(new SQLCharExpr(column.getDefVal()));
                }
                isModify=true;
            }
            if(isModify){
                modifyColumn.getColumns().add(element);
                statement.addItem(modifyColumn);
            }
            //修改注释
            if(!column.getColumnComment().equals(oldColumn.getColumnComment())){
                addCommen(table, commens, column);
            }
        }
        //新增列
        for(;i<columns.size();i++){
            ColumnInfo column = columns.get(i);
            SQLAlterTableAddColumn addColumn = new SQLAlterTableAddColumn();
            SQLColumnDefinition element = new SQLColumnDefinition();
            element.setName("\""+column.getColumnName()+"\"");
            SQLDataTypeImpl sqlDataType = new SQLDataTypeImpl(column.getDataType());
            if(!StringUtils.isBlank(column.getArgs())){
                String[] args = column.getArgs().split(",");
                for (String arg : args) {
                    sqlDataType.addArgument(new SQLIntegerExpr(Integer.parseInt(arg)));
                }
            }
            element.setDataType(sqlDataType);
            if(column.getIsNotNull()){
                element.addConstraint(new SQLNotNullConstraint());
            }
            if(!StringUtils.isBlank(column.getDefVal())){
                element.setDefaultExpr(new SQLCharExpr(column.getDefVal()));
            }
            addColumn.addColumn(element);
            statement.addItem(addColumn);
            addCommen(table, commens, column);
        }
        StringBuilder result = new StringBuilder();
        result.append(statement.toString()).append(";");
        for (String commen : commens) {
            result.append(commen).append(";");
        }
        return result.toString();
    }

    private void addCommen(TableInfo table, ArrayList<String> commens, ColumnInfo column) {
        SQLCommentStatement commentStatement = new SQLCommentStatement();
        commentStatement.setComment(new SQLCharExpr(column.getColumnComment()));
        commentStatement.setType(SQLCommentStatement.Type.COLUMN);
        commentStatement.setOn(new SQLPropertyExpr("\""+table.getTableName()+"\"", "\""+column.getColumnName()+"\""));
        commens.add(commentStatement.toString());
    }

    @Override
    public void modifyTableByDdl(DatabaseInfo database, List<SQLStatement> sqlStatements) throws SQLException {
        Connection conn = getConn();
        Statement statement =null;
        try {
            statement = conn.createStatement();
            String sql = sqlStatements.get(0).toString();
            statement.execute(sql.substring(0, sql.length() - 1));
            for (int i = 1; i < sqlStatements.size(); i++) {
                sql = sqlStatements.get(i).toString();
                statement.execute(sql.substring(0, sql.length() - 1));
            }
        }finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public List<SQLStatement> checkModifyTableDdl(String ddl,String tableName) {
        SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(ddl, JdbcConstants.ORACLE);
        List<SQLStatement> sqlStatements = parser.parseStatementList();
        for (SQLStatement sqlStatement : sqlStatements) {
            if (!(sqlStatement instanceof SQLAlterTableStatement || sqlStatement instanceof SQLCommentStatement)) {
                throw new ParserException("修改语句类型错误");
            }
            if(!tableName.equals(getTableNameByModifyDdl(sqlStatement))){
                throw new ParserException("修改语句表名与原表不匹配");
            }
        }
        return sqlStatements;
    }

    @Override
    public List<String> getDataTypes() {
        String[] dataTypes={"VARCHAR2","CHAR","DATE","LONG","RAW","LONG RAW","BLOB","CLOB","NUMBER","DECIMAL","INTEGER","FLOAT","REAL"};
        return Arrays.asList(dataTypes);
    }

    @Override
    public String format(String sql) {
        return SQLUtils.format(sql, JdbcConstants.ORACLE);
    }

    @Override
    public String getDatasourceDriver() {
        return COM_ORACLE_JDBC_DRIVER;
    }

}
